package com.bringway.p2p.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import net.sf.json.JSONObject;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.p2p.dao.ZqshcgDao;
/**
 * 
 * @author zhai
 * @version 创建时间：2015年12月15日 上午9:34:42
 * @功能：发布债权界面
 */
@Repository
public class ZqshcgDaoImpl extends P2PJdbcDao implements ZqshcgDao{
	
	Logger logger = Logger.getLogger(ZqshcgDaoImpl.class);

	/**
	 * @author zhai
	 * ZqshcgDao的实现类
	 * 获取债权发放的草稿数据集
	 */
	@Override
	public List queryZqshjgList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String xmmct = params.optString("xmmct");
		String jkrt = params.optString("jkrt");
		String state = params.optString("state");
		String dfridt = params.optString("dfridt");
		String xmType = params.optString("xmType");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("XMMC", xmmct);
		paramMap.put("JKR", jkrt);
		paramMap.put("STATE", state);
		paramMap.put("DFRID", dfridt);
		sql.append(" select t1.ID,t1.XMMC,t1.SJSYL,t1.JE,t1.QX,t1.JD,t1.FBSJ,t1.ZT,t1.JKR,t1.DFRID,t1.JKXMJJ,t1.JKYT,t1.DBSM ");
		sql.append(" ,t1.MJQ,t1.JXRQ,t1.STATE,t1.SHSJ,t1.LDU,t1.LSHR,t1.SHBZ,t1.SJSYL_SHOW,t1.XM_TYPE,t1.HK_TYPE,t1.QX_MONTH ");
		sql.append(" ,date_format(SQSJ,'%Y-%m-%d %H:%i:%s') SQSJ,date_format(SHSJ,'%Y-%m-%d %H:%i:%s') SHSJ ");
		sql.append(" ,t1.XM_ZC_TYPE,t1.XM_PGJZ,t1.XM_SYED ");
		sql.append(" ,FD_TYPE_ID,t2.FD_NAME,t1.JKR_CSNF,t1.JKR_ZGXL,t1.JKR_HYZK,t1.JKR_ZNQK,t1.JKR_HJDZ,t1.JKR_ZC_SRSP,t1.JKR_ZC_FCQK,t1.JKR_ZC_XYKZS,t1.JKR_ZC_XYKED ");
		sql.append(", t1.JKR_DW_HY,t1.JKR_DW_XZ,t1.JKR_DW_DZ,t1.JKR_DW_ZW, date_format(JKR_DW_RZSJ,'%Y-%m-%d %H:%i:%s') JKR_DW_RZSJ ");
		sql.append(" FROM t_zhfw_zqzrlb_cg t1 ");
		sql.append(" left join t_zhfw_zqfd t2 on t1.FD_TYPE_ID = t2.ID ");
		sql.append(" WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t1.SQSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t1.SQSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(xmmct)){
			sql.append(" AND t1.XMMC like "+ "'%"+xmmct+"%'");
		}
		if(StringUtils.isNotEmpty(jkrt)){
			sql.append(" AND t1.JKR like "+ "'%"+jkrt+"%'");
		}
		if(StringUtils.isNotEmpty(dfridt)){
			sql.append(" AND t1.DFRID like "+ "'%"+dfridt+"%'");
		}
		
		if((StringUtils.isNotEmpty(state))){
			sql.append(" AND t1.STATE = :STATE ");
			paramMap.put("STATE", state);
		}
		if(StringUtils.isNotEmpty(xmType)){
			sql.append(" AND t1.XM_TYPE = :XM_TYPE ");
			paramMap.put("XM_TYPE", xmType);
		}
		sql.append("order by t1.SQSJ desc LIMIT :START,:LIMIT");
		logger.info("queryZqshjgList sql is : " + sql.toString());
		logger.info("queryZqshjgList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author zhai
	 * 获取债权审核草稿的数据个数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getZqshjCount(JSONObject params) {
        StringBuffer sql = new StringBuffer();
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmmct = params.optString("xmmct");
		String jkrt = params.optString("jkrt");
		String state = params.optString("state");
		String dfridt = params.optString("dfridt");
		String xmType = params.optString("xmType");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(" select count(*) CT from t_zhfw_zqzrlb_cg ");
        sql.append(" where 1=1 ");
        if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(SQSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(SQSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(xmmct)){
			sql.append(" AND XMMC like "+ "'%"+xmmct+"%'");
		}
		if(StringUtils.isNotEmpty(jkrt)){
			sql.append(" AND JKR like "+ "'%"+jkrt+"%'");
		}
		if(StringUtils.isNotEmpty(dfridt)){
			sql.append(" AND DFRID like "+ "'%"+dfridt+"%'");
		}
		if((StringUtils.isNotEmpty(state))){
			sql.append(" AND STATE = :STATE ");
			paramMap.put("STATE", state);
		}
		if(StringUtils.isNotEmpty(xmType)){
			sql.append(" AND XM_TYPE = :XM_TYPE ");
			paramMap.put("XM_TYPE", xmType);
		}
        return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/**
	 * @author zhai
	 * 添加并保存债权内发放审核草稿的数据
	 */
	@Override
	public String saveZqshj(JSONObject params) {
        StringBuffer sql = new StringBuffer();
		String xmmc = params.optString("xmmc");
		String sjsyl = params.optString("sjsyl");
		String je = params.optString("je");
		String qx = params.optString("qx");
		String qxMonth = params.optString("qxMonth");
		String fbsj = params.optString("fbsj");
		String jkr = params.optString("jkr");
		String dfrid = params.optString("dfrid");
		String jkxmjj = params.optString("jkxmjj");
		String jkyt = params.optString("jkyt");
		String dbsm = params.optString("dbsm");
		String mjq = params.optString("mjq");
		String jxrq = params.optString("jxrq");
		String fdType = params.optString("fdType");
		String sjsylShow = params.optString("sjsylShow");
		String xmType = params.optString("xmType");
		String hkType = params.optString("hkType");
		//借款人信息开始2016.6.13
		String jkrCsnf = params.optString( "jkrCsnf");//出生年份
		String jkrZgxl =  params.optString( "jkrZgxl");//最高学历
		String jkrHyzk =  params.optString( "jkrHyzk");//婚姻状况
		String jkrZnqk =  params.optString( "jkrZnqk");//子女情况
		String jkrHjdz =   params.optString( "jkrHjdz");//户籍地址
		String jkrZcSrsp =  params.optString( "jkrZcSrsp");//月收入水平
		String jkrZcFcqk =  params.optString( "jkrZcFcqk");//房产情况
		String jkrZcXykzs =  params.optString( "jkrZcXykzs");//信用卡张数
		String jkrZcXyked =  params.optString( "jkrZcXyked");//信用卡额度
		String jkrDwHy =  params.optString( "jkrDwHy");//行业
		String jkrDwXz =  params.optString( "jkrDwXz");//性质
		String jkrDwDz =  params.optString( "jkrDwDz");//地址
		String jkrDwZw =   params.optString( "jkrDwZw");//职位
		String rzsj =  params.optString( "jkrDwRzsj");//入职时间
		String xmZcType =  params.optString( "xmZcType");//资产类型
		String xmPgjz =  params.optString( "xmPgjz");//评估价值(万)
		String xmSyed =  params.optString( "xmSyed");//授信额度(万)
		Timestamp jkrDwRzsj=null;
		if(StringUtils.isNotEmpty(rzsj)){
			rzsj = rzsj + " 00:00:00";
			jkrDwRzsj = Timestamp.valueOf(rzsj);
		}
		if(StringUtils.isNotEmpty(fbsj)){
			fbsj = fbsj + " 17:00";
		}
		String uuid = this.getUUId();
		sql.append(" INSERT INTO t_zhfw_zqzrlb_cg(ID,XMMC,SJSYL,JE");
		sql.append(" ,QX,JD,FBSJ,ZT,JKR,DFRID,JKXMJJ,JKYT,DBSM,MJQ,JXRQ,STATE,LDU,FD_TYPE_ID,SJSYL_SHOW,XM_TYPE,HK_TYPE ");
		//项目简介-资产类型
		sql.append(" ,XM_ZC_TYPE,XM_PGJZ,XM_SYED ");
		//新增借款人信息
		sql.append(" ,QX_MONTH,JKR_CSNF,JKR_ZGXL,JKR_HYZK,JKR_ZNQK,JKR_HJDZ,JKR_ZC_SRSP,JKR_ZC_FCQK,JKR_ZC_XYKZS,JKR_ZC_XYKED ");
		sql.append(", JKR_DW_HY,JKR_DW_XZ,JKR_DW_DZ,JKR_DW_ZW, JKR_DW_RZSJ )");
		
		sql.append(" VALUES (:UUID,:XMMC,:SJSYL,:JE,:QX");
		sql.append(" ,:JD,:FBSJ,:ZT,:JKR,:DFRID,:JKXMJJ,:JKYT,:DBSM,:MJQ,:JXRQ,:STATE,:LDU,:FD_TYPE_ID,:SJSYL_SHOW,:XM_TYPE,:HK_TYPE ");
		sql.append(" ,:XM_ZC_TYPE,:XM_PGJZ,:XM_SYED ");
		//新增借款人信息
		sql.append(" ,:QX_MONTH,:JKR_CSNF,:JKR_ZGXL,:JKR_HYZK,:JKR_ZNQK,:JKR_HJDZ,:JKR_ZC_SRSP,:JKR_ZC_FCQK,:JKR_ZC_XYKZS,:JKR_ZC_XYKED ");
		sql.append(", :JKR_DW_HY,:JKR_DW_XZ,:JKR_DW_DZ,:JKR_DW_ZW, :JKR_DW_RZSJ )");
		
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("UUID", uuid)
				.addValue("XMMC", xmmc)
                .addValue("SJSYL", sjsyl)
                .addValue("JE", je)
                .addValue("QX", qx)
                .addValue("JD", "0")
                .addValue("ZT", "1")
                .addValue("FBSJ", fbsj)
                .addValue("JKR", jkr)
                .addValue("DFRID", dfrid)
                .addValue("JKXMJJ", jkxmjj)
                .addValue("JKYT", jkyt)
                .addValue("DBSM", dbsm)
                .addValue("MJQ", mjq)
                .addValue("JXRQ", jxrq)
                .addValue("STATE", "01")
                .addValue("LDU", dfrid)
                .addValue("FD_TYPE_ID", fdType)
                .addValue("SJSYL_SHOW", sjsylShow)
                .addValue("XM_TYPE", xmType)
                .addValue("HK_TYPE", hkType)
                .addValue("QX_MONTH", qxMonth)
                //资产类型，授信额度
                .addValue("XM_ZC_TYPE", xmZcType)
                .addValue("XM_PGJZ", xmPgjz)
                .addValue("XM_SYED", xmSyed)
                //新增借款人信息
                .addValue("JKR_CSNF", jkrCsnf)
                .addValue("JKR_ZGXL", jkrZgxl)
                .addValue("JKR_HYZK", jkrHyzk)
                .addValue("JKR_ZNQK", jkrZnqk)
                .addValue("JKR_HJDZ", jkrHjdz)
                .addValue("JKR_ZC_SRSP", jkrZcSrsp)
                .addValue("JKR_ZC_FCQK", jkrZcFcqk)
                .addValue("JKR_ZC_XYKZS", jkrZcXykzs)
                .addValue("JKR_ZC_XYKED", jkrZcXyked)
                .addValue("JKR_DW_HY", jkrDwHy)
                .addValue("JKR_DW_XZ", jkrDwXz)
                .addValue("JKR_DW_DZ", jkrDwDz)
                .addValue("JKR_DW_ZW", jkrDwZw)
                .addValue("JKR_DW_RZSJ", jkrDwRzsj)
                
                ;
		this.logger.info("saveZqshj  sql : " + sql.toString());
		this.logger.info("saveZqshj  parameters : " + parameters.toString());
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
		return uuid;
	}

	/**
	 * @author zhai
	 * 删除债权审核草稿的数据
	 */
	@Override
	public void deleteZqshcg(JSONObject params) {
		String bizId = params.optString("bizId");
		String gyh = params.optString("gyh");
		System.out.println(gyh);
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from t_zhfw_zqzrlb_cg where id = :ID and state = '01' and LDU= :gyh  ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("ID", bizId)
				.addValue("gyh", gyh)
				;
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
	}

	@Override
	public Map<String, Object> getZqInfo(JSONObject params) {
		String bizId = params.optString("bizId");
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from t_zhfw_zqzrlb_cg where id = :ID  ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("ID", bizId);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), parameters);
	}

	
	
	
	
	@Override
	public boolean saveSHZqshcg(JSONObject params) {
		String id = params.optString("id");
		String shr = params.optString("shr");
		String shState = params.optString("shState");
		String shContent = params.optString("shContent");
		if(StringUtils.isEmpty(id)){
			logger.info("saveSHZqshcg id为空 ");
			return false;
		}else if(StringUtils.isEmpty(shState)){
			logger.info("saveSHZqshcg 审核状态为空 ");
			return false;
		}else{
			StringBuffer sql = new StringBuffer();
			Map<String,String> paramMap = new HashMap<>();
			sql.append(" update t_zhfw_zqzrlb_cg ");
			sql.append(" set LSHR =:LSHR,SHSJ = NOW(), STATE = :STATE, SHBZ =:SHBZ");
			sql.append(" where ID = :ID ");
			paramMap.put("LSHR", shr);
			paramMap.put("STATE", shState);
			paramMap.put("SHBZ", shContent);
			paramMap.put("ID", id);
			logger.info("saveSHZqshcg sql is " + sql.toString());
			logger.info("saveSHZqshcg paramMap is " + paramMap);
			this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
			return true;
		}
		
	}

	@Override
	public Map<String, Object> getZqshcg(JSONObject params) {
		Map<String, Object> result = null;
		String id = params.optString("id");
		if(StringUtils.isNotEmpty(id)){
			StringBuffer sql = new StringBuffer();
			Map<String,String> paramMap = new HashMap<>();
			sql.append(" select ID,XMMC,SJSYL,JE,QX,JD,FBSJ,ZT,JKR,DFRID,JKXMJJ,JKYT,DBSM,MJQ,JXRQ,STATE,SHSJ,LDU,LSHR,SHBZ,FD_TYPE_ID,SJSYL_SHOW,XM_TYPE,HK_TYPE,QX_MONTH ");
			sql.append(" ,XM_ZC_TYPE,XM_PGJZ,XM_SYED ");
			//add借款人信息字段
			sql.append(" ,JKR_CSNF,JKR_ZGXL,JKR_HYZK,JKR_ZNQK,JKR_HJDZ,JKR_ZC_SRSP,JKR_ZC_FCQK,JKR_ZC_XYKZS,JKR_ZC_XYKED ");
			sql.append(", JKR_DW_HY,JKR_DW_XZ,JKR_DW_DZ,JKR_DW_ZW, date_format(JKR_DW_RZSJ,'%Y-%m-%d %H:%i:%s') JKR_DW_RZSJ ");
			sql.append(" ,date_format(SQSJ,'%Y-%m-%d %H:%i:%s') SQSJ,date_format(SHSJ,'%Y-%m-%d %H:%i:%s') SHSJ from t_zhfw_zqzrlb_cg ");
			sql.append(" where 1=1 ");
			sql.append(" AND ID = :ID ");
			paramMap.put("ID", id);
			if(this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap).size() > 0){
				result = this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
			}
		}
		return result;
	}

	@Override
	public List<Map<String,String>> loadFdType() {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ID,FD_NAME,FD_SEQ,FD_CONTENT,CREATE_USER_ID,date_format(CREATE_DATE,'%Y-%m-%d %H:%i:%s') CREATE_DATE FROM t_zhfw_zqfd ");
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), new ResultSetExtractor<List<Map<String,String>>>(){
			@Override
			public List<Map<String,String>> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<Map<String,String>> list = new ArrayList<>();
				while(rs.next()){
					Map<String,String> map = new HashMap<>();
					map.put("ID", rs.getString("ID"));
					map.put("FD_NAME", rs.getString("FD_NAME"));
					map.put("FD_SEQ", rs.getString("FD_SEQ"));
					map.put("FD_CONTENT", rs.getString("FD_CONTENT"));
					map.put("CREATE_USER_ID", rs.getString("CREATE_USER_ID"));
					map.put("CREATE_DATE", rs.getString("CREATE_DATE"));
					map.put("comName", rs.getString("FD_NAME"));
					map.put("comCode", rs.getString("ID"));
					list.add(map);
				}
				return list;
			}
		});
	}

	@Override
	public int getQxDayByQxMonth(String qxMonth) {
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<String,String>();
		sql.append(" select datediff(date_add(now(),interval :QX_MONTH month),now())  QX_DAY FROM DUAL ");
		paramMap.put("QX_MONTH", qxMonth);
		logger.info(" getQxDayByQxMonth sql is : " + sql.toString());
		logger.info(" getQxDayByQxMonth paramMap is : " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(),paramMap,Integer.class);
	}
	
	
	
}
